This disclosure relates generally to database management systems, and more specifically, to generating database statistics for a query execution plan based on a transaction state.
Before query execution, database management systems may employ an optimizer engine to determine an efficient or most efficient method to access data requested from a query statement. For example, a cost-based optimizer may generate the best execution plan, which is the plan with the lowest cost among all other candidate plans. When determining an efficient or most efficient plan to access the requested data, the optimizer may utilize various column statistics (e.g., frequent value lists, histograms, cardinality, selectivity, etc.) to track information about column values and data distribution within the columns. These statistics may be foundational for determining whether particular execution plans are implemented.
In some examples, these column statistics may be kept for committed transactions only. A transaction is one or more operations that make up a unit of work performed against a database. For example, an operation to delete a particular database record (i.e., row) from a table and an operation to update another database record may be a transaction. When a transaction is committed, the corresponding operation(s) are executed successfully, (e.g., successfully deleting and updating the database records). When a transaction is uncommitted, the transaction is still in progress and not completed (e.g., a database record is deleted and a database record to be updated has not occurred yet). Queries for data may return database records that are both in a committed and uncommitted transaction state. Alternatively, queries may return database records that are only in a committed transaction state, depending on the locking mechanisms (i.e., isolation levels) utilized within the database.